﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class TitleResultRepo
    {
        public const string m_msg_head = "Title Result";

        public IEnumerable<TitleResultModels> GetTitleResult()
        {
            List<TitleResultModels> TitleResults = new List<TitleResultModels>();
            string SQLCommand = @"select * from TBTitleResult WHERE Status <> 'Delete' Order by TitleResultId";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        TitleResultModels TitleResult = new TitleResultModels();
                        TitleResult.TitleResultId = (int)reader["TitleResultId"];
                        TitleResult.TitleResultName = (string)reader["TitleResultName"];
                        TitleResult.status = (string)reader["status"];

                        TitleResults.Add(TitleResult);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return TitleResults.ToList();
        }

        public TitleResultModels GetTitleResultById(int id)
        {
            TitleResultModels TitleResult = new TitleResultModels();
            string SQLCommand = @"select * from TBTitleResult where TitleResultId=@TitleResultId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@TitleResultId", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        TitleResult.TitleResultId = (int)reader["TitleResultId"];
                        TitleResult.TitleResultName = (string)reader["TitleResultName"];
                        TitleResult.status = (string)reader["status"];

                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return TitleResult;
        }

        public bool Create(TitleResultModels TitleResult)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBTitleResult
                                    values (@TitleResultName,@status)";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleResultName", TitleResult.TitleResultName);
                    db.AddParameter("@status", "Active");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(TitleResultModels TitleResult)
        {
            bool result = true;
            string SQLCommand = @"Update TBTitleResult 
                                    Set TitleResultName=@TitleResultName
                                    where TitleResultId=@TitleResultId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleResultId", TitleResult.TitleResultId);
                    db.AddParameter("@TitleResultName", TitleResult.TitleResultName);
             
                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(TitleResultModels TitleResult)
        {
            bool result = true;
            string SQLCommand = @"Update TBTitleResult
                                    Set status=@status
                                    where TitleResultId=@TitleResultId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@TitleResultId", TitleResult.TitleResultId);
                    db.AddParameter("@status", "Delete");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}